﻿using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Threading;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.ComponentModel;
using System.Data.SqlClient;
namespace WordsMatching
{
    class db
    {
        MySqlConnection myConnection = new MySqlConnection("user id=root;" +
                                            "password=;server=localhost; database=weka;Port=3306");

        int[] trigger_des_id = new int[500];
        string[] trigger_des_text = new string[500];
        string[] trigger_des_keyword = new string[500];
        int trigger_des_limit = 0;

        int[] response_des_id = new int[500];
        string[] response_des_text = new string[500];
        string[] response_des_keyword = new string[500];
        int response_des_limit = 0;

        int[] trigger_id = new int[500];
        int[] trigger_text_des_id = new int[500];
        string[] trigger_text = new string[500];

        int[] response_id = new int[500];
        int[] response_text_des_id = new int[500];
        string[] response_text = new string[500];
        int[] priority = new int[500];

        int[] trigger_binding = new int[500];
        int[] response_binding = new int[500];
        int binding_limit = 0;

        int trigger_limit = 0;
        int response_limit = 0;

        string new_trigger, new_des, new_response, new_trigger_keyword, new_response_keyword;




        public void AddData()
    {
        try
        {


           
                myConnection.Open();

               
                    
                    MySqlCommand myCommand = new MySqlCommand("INSERT INTO data(trigger,utterance,semantic_score,syntactic_score,lev_distance,average_score)values (\"" + new_des + "\");", myConnection);
                    MySqlDataReader Reader = myCommand.ExecuteReader();

              
                Reader.Close();

                Console.Write("new Description added sucessfully");

            
        }
        catch (Exception e)
        {
            Console.WriteLine(e.ToString());
        }


    }

        public void AddDescription(string option)
        {
            Show_Descp(option);
            insert_description(option);

        }

        public void AddTrigger()
        {
            Show_Descp("trigger");
            Console.Write("\n Enter 1 to use existing Description id or enter 0 to create a new description :");
            int choice = Convert.ToInt32(Console.ReadLine());
            string id_choice;
            if (choice == 0)
            {
                AddDescription("trigger");

            }
            else
            {
                Console.Write("Enter Description id from above list :");
                id_choice = Console.ReadLine();
                show_triggers(id_choice);
                Console.Write("\n \n Enter New Trigger:");
                new_trigger = Console.ReadLine();


                try
                {



                    //myConnection.Open();

                    MySqlCommand myCommand = new MySqlCommand("INSERT INTO animesh_trigger_sentences(des_id,trigger_text) values (" + Convert.ToInt32(id_choice) + ",\"" + new_trigger + "\");", myConnection);
                    MySqlDataReader Reader = myCommand.ExecuteReader();
                    Reader.Close();
                    Console.Write("new Trigger added sucessfully");
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.ToString());
                }
            }








        }
        public void AddResponse()
        {
            Show_Descp("response");
            Console.Write("\n Enter 1 to use existing Description id or enter 0 to create a new description :");
            int choice = Convert.ToInt32(Console.ReadLine());
            string id_choice;
            if (choice == 0)
            {
                AddDescription("response");

            }
            else
            {
                Console.Write("Enter Description id from above list :");
                id_choice = Console.ReadLine();
                show_responses(id_choice);
                Console.Write("\n \n Enter New Response:");
                new_response = Console.ReadLine();


                try
                {



                    //myConnection.Open();

                    MySqlCommand myCommand = new MySqlCommand("INSERT INTO animesh_response_sentences(response_id,response_text) values (" + Convert.ToInt32(id_choice) + ",\"" + new_response + "\");", myConnection);
                    MySqlDataReader Reader = myCommand.ExecuteReader();
                    Reader.Close();
                    Console.Write("new Response added sucessfully");
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.ToString());
                }
            }

        }

        public void get_binding_ids()
        {
            try
            {

                myConnection.Open();

                MySqlCommand myCommand = new MySqlCommand("select id,response_text from animesh_response_sentences ;", myConnection);
                MySqlDataReader Reader = myCommand.ExecuteReader();
                Console.WriteLine("\n \n \n \n \n \n \n Response ID          Response ");
                Console.WriteLine("----------------------------------------------------------------------");
                while (Reader.Read())
                {
                    for (int i = 0; i < Reader.FieldCount; i++)
                    {
                        Console.Write(Reader.GetValue(i).ToString());
                        Console.Write("       \n ");
                    }

                }
                Reader.Close();

            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
            myConnection.Close();

            Console.Write("\n  Enter Response Id :");
            int res_id_choice = Convert.ToInt32(Console.ReadLine());


            try
            {

                myConnection.Open();

                MySqlCommand myCommand = new MySqlCommand("select trigger_id,trigger_text from animesh_trigger_sentences ;", myConnection);
                MySqlDataReader Reader = myCommand.ExecuteReader();
                Console.WriteLine("\n \n \n \n \n \n \n Trigger ID          Trigger ");
                Console.WriteLine("----------------------------------------------------------------------");
                while (Reader.Read())
                {
                    for (int i = 0; i < Reader.FieldCount; i++)
                    {
                        Console.WriteLine(Reader.GetValue(i).ToString());
                        Console.Write("       \n ");

                    }

                }
                Reader.Close();

            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
            myConnection.Close();
            Console.Write("\n \n \n Enter Trigger Id :");
            int tri_id_choice = Convert.ToInt32(Console.ReadLine());
            MakeBinding(res_id_choice, tri_id_choice);

        }

        public void MakeBinding(int res_id, int tri_id)
        {

            try
            {

                myConnection.Open();

                MySqlCommand myCommand = new MySqlCommand("insert into animesh_response_trigger_bindings (trigger_id,response_id) values (" + tri_id + "," + res_id + ");", myConnection);
                MySqlDataReader Reader = myCommand.ExecuteReader();
                Console.WriteLine("\n  Binding committed successfully ");

            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
            myConnection.Close();
        }

        void insert_description(string option)
        {
            Console.Write("Enter new Description:");
            string new_des = Console.ReadLine();

            try
            {


                if (option == "trigger")
                {
                    myConnection.Open();

                    MySqlCommand myCommand = new MySqlCommand("INSERT INTO animesh_triggers(des_text) values (\"" + new_des + "\");", myConnection);
                    MySqlDataReader Reader = myCommand.ExecuteReader();
                    Reader.Close();
                    Console.Write("new Description added sucessfully");

                }
                if (option == "response")
                {
                    myConnection.Open();

                    MySqlCommand myCommand = new MySqlCommand("INSERT INTO animesh_responses(des_text) values (\"" + new_des + "\");", myConnection);
                    MySqlDataReader Reader = myCommand.ExecuteReader();
                    Reader.Close();
                    Console.Write("new Description added sucessfully");
                }

            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }

        }

        public void Show_Descp(string option)
        {

            try
            {

                if (option == "trigger")
                {
                    myConnection.Open();
                    MySqlCommand myCommand = new MySqlCommand("select id,des_text from animesh_triggers", myConnection);
                    MySqlDataReader Reader = myCommand.ExecuteReader();
                    while (Reader.Read())
                    {
                        for (int i = 0; i < Reader.FieldCount; i++)
                        {
                            // Console.WriteLine(Reader.GetValue(i).ToString());
                            trigger_des_id[trigger_des_limit] = Convert.ToInt32(Reader.GetString(0));
                            trigger_des_text[trigger_des_limit] = Reader.GetString(1);
                            trigger_des_limit++;
                        }

                    }
                    Reader.Close();
                    Console.WriteLine("\n \n \n \n \n  ID        Trigger Description  ");
                    Console.WriteLine("-------------------------------------------------");
                    for (int i = 0; i < trigger_des_limit; i++)
                    {
                        Console.Write(trigger_des_id[i]);
                        Console.WriteLine("  " + (trigger_des_text[i]));
                    }

                }

                if (option == "response")
                {
                    myConnection.Open();
                    MySqlCommand myCommand = new MySqlCommand("select id,des_text from animesh_responses", myConnection);
                    MySqlDataReader Reader = myCommand.ExecuteReader();
                    while (Reader.Read())
                    {
                        for (int i = 0; i < Reader.FieldCount; i++)
                        {
                            // Console.WriteLine(Reader.GetValue(i).ToString());
                            response_des_id[response_des_limit] = Convert.ToInt32(Reader.GetString(0));
                            response_des_text[response_des_limit] = Reader.GetString(1);
                            response_des_limit++;
                        }

                    }
                    Reader.Close();
                    Console.WriteLine("\n \n \n \n \n  ID        Response Description  ");
                    Console.WriteLine("-------------------------------------------------");
                    for (int i = 0; i < response_des_limit; i++)
                    {
                        Console.Write(response_des_id[i]);
                        Console.WriteLine("  " + (response_des_text[i]));
                    }
                }





            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }

            myConnection.Close();



        }

        private void get_id(string option)
        {
            Console.Write("\n Enter 1 to use existing Description id or enter 0 to create a new description :");
            int choice = Convert.ToInt32(Console.ReadLine());
            string id_choice;
            if (choice == 0)
            {
                AddDescription("trigger");

            }
            else
            {
                Console.Write("Enter Description id from above list :");
                id_choice = Console.ReadLine();

                show_triggers(id_choice);

            }

        }

        private void exe_insert(string cmd)
        {
            try
            {



                myConnection.Open();

                MySqlCommand myCommand = new MySqlCommand("INSERT INTO animesh_triggers(description) values (\"" + cmd + "\");", myConnection);
                MySqlDataReader Reader = myCommand.ExecuteReader();
                Reader.Close();

            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
        }

        public void show_triggers(string id_choice)
        {
            try
            {

                for (int i = 0; i < trigger_des_limit; i++)
                {
                    if (Convert.ToInt32(id_choice) == trigger_des_id[i])
                    {
                        Console.WriteLine("Triggers matching Description \"" + (trigger_des_text[i]) + "\"");

                    }
                }
                myConnection.Open();

                MySqlCommand myCommand = new MySqlCommand("select trigger_text from animesh_trigger_sentences where des_id=\"" + id_choice + "\";", myConnection);
                MySqlDataReader Reader = myCommand.ExecuteReader();
                while (Reader.Read())
                {
                    for (int i = 0; i < Reader.FieldCount; i++)
                    {
                        Console.WriteLine(Reader.GetValue(i).ToString());

                    }

                }
                Reader.Close();

            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }




        }

        public string get_new_trigger()
        {
            Console.Write("\n \n Enter New Trigger:");
            new_trigger = Console.ReadLine();
            return new_trigger;
        }



        public void show_responses(string id_choice)
        {
            try
            {

                for (int i = 0; i < response_des_limit; i++)
                {
                    if (Convert.ToInt32(id_choice) == response_des_id[i])
                    {
                        Console.WriteLine("Responses matching Description \"" + (response_des_text[i]) + "\"");

                    }
                }
                myConnection.Open();

                MySqlCommand myCommand = new MySqlCommand("select response_text from animesh_response_sentences where response_id=\"" + id_choice + "\";", myConnection);
                MySqlDataReader Reader = myCommand.ExecuteReader();
                while (Reader.Read())
                {
                    for (int i = 0; i < Reader.FieldCount; i++)
                    {
                        Console.WriteLine(Reader.GetValue(i).ToString());

                    }

                }
                Reader.Close();

            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
        }

        public void Build_Interaction_Script()
        {

            //Get Trigger Description Data
            myConnection.Open();
            try
            {



                MySqlCommand myCommand = new MySqlCommand("select id,des_text from animesh_triggers ;", myConnection);
                MySqlDataReader Reader = myCommand.ExecuteReader();

                while (Reader.Read())
                {
                    for (int i = 0; i < Reader.FieldCount; i++)
                    {
                        trigger_des_id[trigger_des_limit] = Convert.ToInt32(Reader.GetString(0));
                        trigger_des_text[trigger_des_limit] = Reader.GetString(1);

                        trigger_des_limit++;
                    }

                }
                Reader.Close();

            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
            // myConnection.Close();

            // Get trigger sentence data

            try
            {

                //  myConnection.Open();

                MySqlCommand myCommand = new MySqlCommand("select trigger_id,des_id,trigger_text from animesh_trigger_sentences ;", myConnection);
                MySqlDataReader Reader = myCommand.ExecuteReader();

                while (Reader.Read())
                {
                    for (int i = 0; i < Reader.FieldCount; i++)
                    {
                        trigger_id[trigger_limit] = Convert.ToInt32(Reader.GetString(0));
                        trigger_text_des_id[trigger_limit] = Convert.ToInt32(Reader.GetString(1));
                        trigger_text[trigger_limit] = Reader.GetString(2);
                        trigger_limit++;
                    }

                }
                Reader.Close();

            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
            //myConnection.Close();

            // Get response description data 

            try
            {

                //  myConnection.Open();

                MySqlCommand myCommand = new MySqlCommand("select id,des_text from animesh_responses ;", myConnection);
                MySqlDataReader Reader = myCommand.ExecuteReader();

                while (Reader.Read())
                {
                    for (int i = 0; i < Reader.FieldCount; i++)
                    {
                        response_des_id[response_des_limit] = Convert.ToInt32(Reader.GetString(0));
                        response_des_text[response_des_limit] = Reader.GetString(1);

                        response_des_limit++;
                    }

                }
                Reader.Close();

            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
            //myConnection.Close();

            // get response sentence data

            try
            {

                //  myConnection.Open();

                MySqlCommand myCommand = new MySqlCommand("select id,response_id,response_text from animesh_response_sentences ;", myConnection);
                MySqlDataReader Reader = myCommand.ExecuteReader();

                while (Reader.Read())
                {
                    for (int i = 0; i < Reader.FieldCount; i++)
                    {
                        response_id[response_limit] = Convert.ToInt32(Reader.GetString(0));
                        response_text_des_id[response_limit] = Convert.ToInt32(Reader.GetString(1));
                        response_text[response_limit] = Reader.GetString(2);
                        response_limit++;
                    }

                }
                Reader.Close();

            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
            // myConnection.Close();

            // Get binding

            try
            {

                //   myConnection.Open();

                MySqlCommand myCommand = new MySqlCommand("select trigger_id,response_id from animesh_response_trigger_bindings ;", myConnection);
                MySqlDataReader Reader = myCommand.ExecuteReader();

                while (Reader.Read())
                {
                    for (int i = 0; i < Reader.FieldCount; i++)
                    {
                        trigger_binding[binding_limit] = Convert.ToInt32(Reader.GetString(0));
                        response_binding[binding_limit] = Convert.ToInt32(Reader.GetString(1));
                        binding_limit++;
                    }

                }
                Reader.Close();

            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
            myConnection.Close();

            Console.WriteLine("\n \n Data Retrieved from database successfully..........");
        }

        public void test()
        {
            string[] msg = new string[200];
            int count = 0;
            myConnection.Open();












            try
            {



                MySqlCommand myCommand = new MySqlCommand("select message from message where message_id<100 AND user_name=\"Ryan Smith\" order by post_time DESC;", myConnection);
                MySqlDataReader Reader = myCommand.ExecuteReader();

                while (Reader.Read())
                {
                    for (int i = 0; i < Reader.FieldCount; i++)
                    {
                        msg[count] = Reader.GetString(i);
                        //Console.WriteLine(Reader.GetString(i));
                        count++;
                    }

                }
                Reader.Close();

            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }


            try
            {



                //myConnection.Open();

                MySqlCommand myCommand = new MySqlCommand("INSERT INTO message(chat_id,user_id,user_name,message,post_time) values(" + 1 + "," + 1 + ",'Ryan Smith','" + msg[0] + "',NOW());", myConnection);
                MySqlDataReader Reader = myCommand.ExecuteReader();
                Reader.Close();
                //Console.Write("new Trigger added sucessfully");
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }




            myConnection.Close();
        }

    }//end of class
}